Did not find gp_relation_node entry for relation name pg_aocsseg_45264, relation id 45268, tablespaceOid 0, relfilenode 28765 (relcache.c:664)
gp_vmem = ((SWAP + RAM) – (7.5GB + 0.05 * RAM)) / 1.7 gp_vmem_protect_limit = gp_vmem / max_acting_primary_segments
VMware Tanzu™ Greenplum® 6.17.2 release is available to "All Users" on TanzuNet."Err:6 http://ppa.launchpad.net/greenplum/db/ubuntu hirsute Release 404 Not Found [IP: 91.189.95.85 80]"
sudo add-apt-repository ppa:greenplum/db [sudo] password for fmickey: Repository: 'deb http://ppa.launchpad.net/greenplum/db/ubuntu/ hirsute main' Description: Greenplum Database is a massively parallel processing (MPP) database server that supports next generation data warehousing and large-scale analytics processing. By automatically partitioning data and running parallel queries, it allows a cluster of servers to operate as a single database supercomputer performing tens or hundreds times faster than a traditional database. It supports SQL, MapReduce parallel processing, and data volumes ranging from hundreds of gigabytes, to hundreds of terabytes. How to use this PPA: https://greenplum.org/install-greenplum-oss-on-ubuntu/ Documentation: https://docs.greenplum.org/ More info: https://launchpad.net/~greenplum/+archive/ubuntu/db Adding repository. Press [ENTER] to continue or Ctrl-c to cancel. Found existing deb entry in /etc/apt/sources.list.d/greenplum-ubuntu-db-hirsute.list Adding deb entry to /etc/apt/sources.list.d/greenplum-ubuntu-db-hirsute.list Found existing deb-src entry in /etc/apt/sources.list.d/greenplum-ubuntu-db-hirsute.list Adding disabled deb-src entry to /etc/apt/sources.list.d/greenplum-ubuntu-db-hirsute.list Adding key to /etc/apt/trusted.gpg.d/greenplum-ubuntu-db.gpg with fingerprint 20EB196A6A0C3FB6D1073F913C6FDC0C01D86213 Hit:1 http://us.archive.ubuntu.com/ubuntu hirsute InRelease Ign:2 http://ppa.launchpad.net/greenplum/db/ubuntu hirsute InRelease Hit:3 http://us.archive.ubuntu.com/ubuntu hirsute-updates InRelease Hit:4 http://security.ubuntu.com/ubuntu hirsute-security InRelease Hit:5 http://us.archive.ubuntu.com/ubuntu hirsute-backports InRelease Err:6 http://ppa.launchpad.net/greenplum/db/ubuntu hirsute Release 404 Not Found [IP: 91.189.95.85 80] Reading package lists... Done E: The repository 'http://ppa.launchpad.net/greenplum/db/ubuntu hirsute Release' does not have a Release file. N: Updating from such a repository can't be done securely, and is therefore disabled by default. N: See apt-secure(8) manpage for repository creation and user configuration details.
VMware Tanzu™ Greenplum® Backup and Restore 1.21.0 release is available to "All Users" on TanzuNet.diskquota is not able to fit the goal since it is soft limit and can only limit the fixed size on role/schema basis.postgres=# create table foopart (a int, b int) distributed by (a) partition by range(a) (start(1) end(3) every(1));
NOTICE: CREATE TABLE will create partition "foopart_1_prt_1" for table "foopart"
NOTICE: CREATE TABLE will create partition "foopart_1_prt_2" for table "foopart"
CREATE TABLE
postgres=# insert into foopart select i%2+1, i from generate_series(1,50)i;
INSERT 0 50
postgres=# select gp_toolkit.gp_skew_details('foopart_1_prt_1'::regclass);
gp_skew_details
-----------------
(16387,2,0)
(16387,0,0)
(16387,1,25)
(3 rows)
postgres=# select gp_toolkit.gp_skew_details('foopart_1_prt_2'::regclass);
gp_skew_details
-----------------
(16392,0,25)
(16392,1,0)
(16392,2,0)
(3 rows)gpadmin=# \d pxf_trino_memory_names
External table "public.pxf_trino_memory_names"
Column | Type | Modifiers
--------+-----------------------------+-----------
id | integer |
now | timestamp without time zone |
Type: readable
Encoding: UTF8
Format type: custom
Format options: formatter 'pxfwritable_import'
External options: {}
External location: pxf://postgresql.public.trino_test?PROFILE=jdbc&SERVER=trino
Execute on: all segments
gpadmin=# SELECT * from pxf_trino_memory_names limit 3;
id | now
----+---------------------------
1 | 2021-09-21 16:35:55.67971
2 | 2021-09-21 16:35:55.67971
3 | 2021-09-21 16:35:55.67971
(3 rows)jdbc-site.xmlSSLKeyStorePath and it worked. LOL. <property>
<name>jdbc.connection.property.SSLKeyStorePath</name>
<value>/usr/local/pxf-gp5/servers/trino/trino.matt.lab.pem</value>
</property>
VMware Tanzu™ Greenplum® 6.17.4 release is available to "All Users" on TanzuNet.
VMware Tanzu™ Greenplum® 5.29.0 release is available to "All Users" on TanzuNet.
VMware Tanzu™ Greenplum® 6.17.7 release is available to "All Users" on TanzuNet.

-- FUNCTION: cntrl.fn_delta_load_wrapper(text, text, text, text, character)
-- DROP FUNCTION cntrl.fn_delta_load_wrapper(text, text, text, text, character);
CREATE OR REPLACE FUNCTION cntrl.fn_delta_load_wrapper(
If use gpcopy, does the source table and target table need to be identical?
Does it for creating target in GPDB6 with partition while not with source table in GPDB5?
So the question is how to load data from a no-partitioned table into a partitioned table.
--parallelize-leaf-partitions to false
(edited) # SELECT * FROM gp_toolkit.gp_bloat_diag;
bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages | bdidiag
----------+------------+--------------+-------------+-------------+---------------------------------------
1249 | pg_catalog | pg_attribute | 520 | 32 | significant amount of bloat suspectedscanned 520 of 520 pages, containing 4731 live rows and 0 dead rows; 4731 rows in sample, 4731 estimated total rowsWe found this Jira https://jira-pivotal.atlassian.net/browse/MPP-31297 and suspect the result of gp_bloat_diag is wrong:# select * from gp_bloat_expected_pages_618 where btdrelid = 'pg_attribute'::regclass::oid;
btdrelid | btdrelpages | btdexppages
----------+-------------+-------------
1249 | 520 | 32------ GP5 -----
gpadmin=# SELECT * from gp_toolkit.gp_bloat_diag where bdirelid = 1249;
bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages | bdidiag
----------+------------+--------------+-------------+-------------+---------------------------------------
1249 | pg_catalog | pg_attribute | 544 | 43 | significant amount of bloat suspected
(1 row)
gpadmin=# VACUUM pg_attribute;
gpadmin=# SELECT * from gp_toolkit.gp_bloat_diag where bdirelid = 1249;
bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages | bdidiag
----------+------------+------------+-------------+-------------+---------
(0 rows)
------ GP6: -----
gpadmin=# SELECT * from gp_toolkit.gp_bloat_diag where bdirelid = 1249;
bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages | bdidiag
----------+------------+--------------+-------------+-------------+---------------------------------------
1249 | pg_catalog | pg_attribute | 390 | 16 | significant amount of bloat suspected
gpadmin=# vacuum FREEZE pg_attribute;
VACUUM
gpadmin=# analyze pg_attribute;
ANALYZE
gpadmin=# SELECT * from gp_toolkit.gp_bloat_diag where bdirelid = 1249;
bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages | bdidiag
----------+------------+--------------+-------------+-------------+---------------------------------------
1249 | pg_catalog | pg_attribute | 390 | 16 | significant amount of bloat suspected
VMware Tanzu™ Greenplum® 5.29.1 release is available to "All Users" on TanzuNet.gpmon gpperfmon and gpmon connections to the database on 5X? I tried setting the following GUCS and restarting the database and still no good 
gp_enable_query_metrics = off and gp_enable_gpperfmon=offIm attempting to find information here:sudo sshd -t might reveal more info
VMware Tanzu™ Greenplum® Command Center 6.6.0 release is available to "All Users" on TanzuNet.2021-11-23 19:43:46.528056 UTC,"ajones2","ajones2",p12654,th-1991804800,"127.0.0.1","53756",2021-11-23 19:43:42 UTC,0,con17,,seg-1,,,,sx1,"LOG","00000","LDAP login failed for user ""uid=ajones2,ou=people,dc=dc1,dc=nebula,dc=local"" on server ""192.168.1.82"": Invalid credentials",,,,,,,0,,"auth.c",2384, 2021-11-23 19:43:46.528139 UTC,"ajones2","ajones2",p12654,th-1991804800,"127.0.0.1","53756",2021-11-23 19:43:42 UTC,0,con17,,seg-1,,,,sx1,"FATAL","28000","LDAP authentication failed for user ""ajones2""","Connection matched pg_hba.conf line 92: ""host all ajones2 0.0.0.0/0 ldap ldapserver=192.168.1.82 ldapbasedn=""ou=people,dc=dc1,dc=nebula,dc=local"" ldapbinddn=""cn=admin,dc=dc1,dc=nebula,dc=local"" ldapbindpasswd=""SuperSecretPassword"" ldapsearchattribute=""uid"" """,,,,,,0,,"auth.c",318,


VMware Tanzu™ Greenplum® Backup and Restore 1.23.0 release is available to "All Users" on TanzuNet.--single-data-file based backups, with gpbackup and gprestore, this release reduces the accumulated time spent opening COPY… ON SEGMENT commands… For customers will a smaller number of tables in a database, this may not be too noticeable, but for a recent customer with > 300,000 tables in their database, the sum of this overhead added hours to the backup duration. Details of the --copy-queue-size flag can be found in the user guide and applies to both https://gpdb.docs.pivotal.io/backup-restore/1-23/backup-restore.html
Managing Tanzu Greenplum Database with Greenplum Command Center course is now live on both VMware Customer Connect Learning (VCCL) (Basic Subscription) and Hive. (edited) pg_extprotocol but in document there is no protocol named like https3. for http server which enabled authentication, like httpd basic authentication. usually we can pass the user/password to URL like: http://user:password@xxx.com/@xxx.com as hostname. is that means we did not support http server which required login? here is an example:--- wget example # wget http://192.168.6.198/httpwithpass/data.csv ... HTTP request sent, awaiting response... 401 Unauthorized Authorization failed. # wget http://matt:abc123@192.168.6.198/httpwithpass/data.csv ... Reusing existing connection to 192.168.6.198:80. HTTP request sent, awaiting response... 200 OK --- gpdb example: CREATE EXTERNAL WEB TABLE test_http_with_pass (id int, name varchar(10), address varchar(30)) LOCATION ( 'http://matt:abc123@192.168.6.198/httpwithpass/data.csv' ) FORMAT 'CSV'; gpadmin=# select * from test_http_with_pass; WARNING: could not translate host name "@192.168.6.198", port "80" to address: Name or service not known (seg0 slice1 192.168.6.198:20000 pid=129215) ERROR: hostname cannot be resolved 'http://matt:abc123@192.168.6.198/httpwithpass/data.csv' (seg0 slice1 192.168.6.198:20000 pid=129215) gpadmin=#
VMware Tanzu™ Greenplum® 6.19.0 release is available to "All Users" on TanzuNet.
Hello Team! VMware Tanzu Greenplum 6.19.0
is now available to all customers! Congratulations and thanks to the following teams for the efforts involved in making this happen: gptransfer to migrate their raster data for PostGIS from source cluster (GP5) to destination cluster (GP6), they faced cannot enlarge string buffer containing error.https://community.pivotal.io/s/article/Cannot-Enlarge-String-Buffer?language=en_USAs far as we read the above article, it looks an error caused by the shortage in string buffer (not the shortage in memory for binary data?).The customer and their partner, TED gave up to solve the error due to their restriction in time, and decided to go with pg_dump / pg_restore which they could confirm workable. But after a while, they found some data loading takes longer than they originally expected, and they hope to complete data loading more quickly.cannot enlarge string buffer containing error in gptransfer for PostGIS raster data migration?https://github.com/greenplum-db/gpdb-postgres-merge/blob/master/gpMgmt/bin/gptransferUnfortunately, they can’t reproduce the issue for now because they are still running pg_dump/pg_restore. So, the partner doesn’t raise a case.pg_dump instead of gpbackup, but they might have faced cannot enlarge string buffer containing in gpbackup, too.Found 2 gp_persistent_relation_node entries of {'tablespace_oid': 29292, 'segment_file_num': 2689, 'database_oid': 16384, 'relfilenode_oid': 24370607} on segment {121} I see that's for gp_persistent_relation_node, I wasn't able to find any comparable examples for this catalog error in the docs so I'm wondering if this is maybe related to persistent table errors or something... it looks like one of the the segments, 121, the table gp_persistent_relation_node got doubled somehow
VMware Tanzu™ Greenplum® 5.29.3 release is available to "All Users" on TanzuNet.Our current version of GP is 6.18 and we are going to upgrade to 6.19. However, according to https://gpdb.docs.pivotal.io/6-19/analytics/postGIS.html, the latest version of 6.19 does not come with the required GEOS >= 3.5.0. Do you have a time frame that GEOS >= 3.5.0 will be available, or any suggestion on how to make GEOS >= 3.5.0 functional with 6.19?
Greenplum Database v 6.19 supports the PostGIS extension with these component versions. PostGIS 2.5.4 Proj 4.8.0 Geos 3.4.2 GDAL 1.11.1 Json 0.12 Expat 2.1.0
VMware Tanzu™ Greenplum® 6.19.1 release is available to "All Users" on TanzuNet.We thought we were done, because finished==true, but libpq says we are still busy

0... (59 kB)
1 year agoThere are 3 users - gpadmin, usr1, usr2 All user have access to ROLE gr; grant gr to user1; grant gr to user2;
create schema sch_gr authorization gr; grant all on schema sch_gr to gr;
psql dev -U gpadmin -c "create table sch_gr.t0 (a int) distributed by (a);" psql dev -U usr1 -h localhost -c "create table sch_gr.t1 (a int) distributed by (a);" psql dev -U usr2 -h localhost -c "create table sch_gr.t2 (a int) distributed by (a);"
psql dev -U usr2 -h localhost
dev=> select count(*) from sch_gr.t0;
ERROR: permission denied for relation t0
dev=> select count(*) from sch_gr.t1;
ERROR: permission denied for relation t1
dev=> select count(*) from sch_gr.t2;
count
-------
0
(1 row)dev=> drop table sch_gr.t0; DROP TABLE dev=> drop table sch_gr.t1; DROP TABLE dev=> drop table sch_gr.t2; DROP TABLE dev=>
1 year ago
Keeping the bloat out for heavily modified table will always help. Just depends how one achieves that outcome and for which tables.I have heard mostly in GPDB catalog being the issue and hence MS and such customers have scheduled vacuums via master (not directly on segments) for catalog bloat. (edited) 
PGOPTIONS='-c gp_session_role=utility' psql -d sf_prd_re -p $port -h $hostname -xtc "vacuum analyze .... "
VMware Tanzu™ Greenplum® 6.19.2 release is available to "All Users" on TanzuNet.
VMware Tanzu™ Greenplum® 5.29.4 release is available to "All Users" on TanzuNet.
VMware Tanzu™ Greenplum® Upgrade 1.3.0 release is available to "All Users" on TanzuNet.
VMware Tanzu™ Greenplum® 6.19.4 release is available to "All Users" on TanzuNet.
Check out the latest Greenplum Drumbeat on Confluence with program, product, customer, and team updates.Windows x64: Windows 8, Windows 10, Windows Server 20016
Windows Server (x86), version 2008, 2012, 2016, 2019 and higher
It has already been done for Enterprise products. And it is not planned for the Community edition.
; post below any questions you have for her 
VMware Tanzu™ Greenplum® Command Center 6.6.2 release is available to "All Users" on TanzuNet.
VMware Tanzu™ Greenplum® 6.20.3 release is available to "All Users" on TanzuNet.dca_gpdb_6.x_installer (edited) 1. When is Greenplum officially supporting RHEL 8.x? >>> RHEL 8 is supported in 6.20.x and above 2. Do we have ETA for the GPDB 5.26.x compatibility with RHEL 8.x 3. Is it ok to use RHEL 7.x load/client utilities binary and use it on RHEL 8.x? 4. Any other clients had the similar challenge and how they are able to address it. ?
gpmovemirrors. Do we have any best practice to reallocate each segment across 24 hosts with Spread mirroring? Can it be done automatically? or at least do we have any script that can generate move_config_file with balanced allocation? or it can be done only manually? Assuming number of segments will remain same
VMware Tanzu™ Greenplum® Streaming Server 1.7.2 release is available to "All Users" on TanzuNet.
Check out the latest Greenplum Drumbeat on Confluence with program, product, customer, and team updates!Petabyte-scale data analytics platforms are built on two principles: a) scale-out 'cloud' storage (e.g. S3[1]) with practically infinite bandwidth, wherein every data object is globally identifiable and accessible; b) separate scale-out compute infrastructure where data processing is distributed across multiple nodes to achieve scale. Implementing consistency, or any other transactional property, in such environments requires tight coordination across many compute nodes. It quickly becomes a scale and performance bottleneck. By sacrificing transactions, older systems like Hadoop[38] simply move the burden of consistency to the end users[23]. Other existing solutions range from limiting the fan-out of compute infrastructure (scale-out data warehouses like Snowflake[24] and Greenplum[33] to limiting the scope of transactions to a single table like Delta Lake[19]. This paper introduces SmartPart, a novel partitioning approach for data processing that supports full transactional semantics while it delivers high throughput at low resource utilization. The key insight is that typical transactions reference a small subset of the tables in the system. The proposed algorithm uses analysis of transactions to dynamically assign tables to nodes to statistically minimize cross-node coordination (2-phase commit)[18] for the system's workload over time. The algorithm has been implemented in Project Taurus. Preliminary results from analytical workloads show 2.97x and 4.64x performance benefits for random and SuperCollider workload, respectively.
Recent development of deep learning makes text-to-SQL, translation of natural language questions (utterances or texts) to executable SQL queries, more than a moon shot. In this paper, we present a practical end-to-end text-to-SQL solution that addresses the technical barriers and meets the functional requirements for building an enterprise-grade text-to-SQL application. At the core of the solution, we propose a dual-model translation scheme that utilizes the state-of-the-art pretrained language models like GPT-3 and BERT to perform the text-to-SQL translation. In addition, we conduct well-designed experiments to verify the hypothesis that we make in the proposed solution. To demonstrate the effectiveness of the solution, we build a text-to-SQL application for EUC's Customer 360 Database, which allows stakeholders like sales representatives and go-to-market (GTM) managers to directly access EUC customer data with natural language and boost their productivities.
returning syntax
gpload-like) with multiple table targets? Yes, I could see why folks would ask for something like that (edited)
). They're looking to have similar functionality in Greenplum and as it stands today I know Greenplum doesn't have a replication play. So a few questions for folks$ gppkg -q --all 20220614:17:41:45:001657 gppkg:gp-ansible-1:gpadmin-[INFO]:-Starting gppkg with args: -q --all pivotal_greenplum_backup_restore-1.24.0 gpss-gpdb6
$ gppkg -q pivotal_greenplum_backup_restore-1.24.0 20220614:17:44:26:001666 gppkg:gp-ansible-1:gpadmin-[INFO]:-Starting gppkg with args: -q pivotal_greenplum_backup_restore-1.24.0 20220614:17:44:26:001666 gppkg:gp-ansible-1:gpadmin-[ERROR]:-Cannot find package pivotal_greenplum_backup_restore-1.24.0 20220614:17:44:26:001666 gppkg:gp-ansible-1:gpadmin-[CRITICAL]:-gppkg failed. (Reason='') exiting...
$ gppkg -q $GPHOME/share/packages/archive/pivotal_greenplum_backup_restore-1.24.0-gp6-rhel-x86_64.gppkg 20220614:17:50:48:001716 gppkg:gp-ansible-1:gpadmin-[INFO]:-Starting gppkg with args: -q /usr/local/greenplum-db-6.20.4/share/packages/archive/pivotal_greenplum_backup_restore-1.24.0-gp6-rhel-x86_64.gppkg 20220614:17:50:48:001716 gppkg:gp-ansible-1:gpadmin-[INFO]:-Validating rpm installation cmdStr='rpm --test -i /usr/local/greenplum-db-6.20.4/.tmp/gpbackup_tools_RHEL-1.24.0-1.x86_64.rpm --dbpath /usr/local/greenplum-db-6.20.4/share/packages/database --prefix /usr/local/greenplum-db-6.20.4' gpbackup_tools is installed. $ gppkg -q $GPHOME/share/packages/archive/gpss-gpdb6-1.7.2-rhel7-x86_64.gppkg 20220614:17:50:57:001727 gppkg:gp-ansible-1:gpadmin-[INFO]:-Starting gppkg with args: -q /usr/local/greenplum-db-6.20.4/share/packages/archive/gpss-gpdb6-1.7.2-rhel7-x86_64.gppkg 20220614:17:50:57:001727 gppkg:gp-ansible-1:gpadmin-[INFO]:-Validating rpm installation cmdStr='rpm --test -i /usr/local/greenplum-db-6.20.4/.tmp/gpss-1.7.2.x86_64.rpm --dbpath /usr/local/greenplum-db-6.20.4/share/packages/database --prefix /usr/local/greenplum-db-6.20.4' 20220614:17:50:57:001727 gppkg:gp-ansible-1:gpadmin-[CRITICAL]:-gppkg failed. (Reason=''gpss-1.7.2-1.x86_64.rpm'') exiting...
CREATE EXTERNAL TABLE test(DT_MONTH date, FLD_002 varchar(250), cnt_oper int, cnr_err int)
LOCATION ('pxf://server:9000/TEST_FOLDER/TEST?PROFILE=Jdbc&SERVER=hivejdbc')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
VMware Tanzu™ Greenplum® Backup and Restore 1.25.0 release is available to "All Users" on TanzuNet.Anything Greenplum and everyone is welcome to participate!When is it:Hacking happens on Wed July 27 and Thurs July 28 during normal business hours for your region.Final Presentations will be on Friday July 29! More details to come. Keep a lookout for a future calendar invite!Who can participate:Any VMware employee! Engineers, Docs, Designers, Support, Field, PMs, etc are all welcome to join! You don’t need to know how to code to join as we welcome both code and no-code projects.This is a global, virtual event so you can participate from anywhere in any timezone. We highly encourage cross-geo teams for around-the-clock hacking!Signing Up:You can sign up for the Hackathon here! Feel free to add your ideas even before you have a set team! We recommend team sizes of 2 – 4 individuals.Theme:The theme is “Anything Greenplum”. Any idea that touches Greenplum in any way is welcome for this Hackathon! Examples of ideas can be found hereStill have questions?Check out our confluence page for more details. join the #gp-hackathon slack channel to stay up to date on all the news and join in on the fun!Let’s get hacking!GP Hackathon Team (edited)
gpupgrade 1.4.0 is Live
Release: https://network.pivotal.io/products/greenplum-upgrade/
12 months agoselect pg_relation_size(schemaname||'.'||tablename),pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)), schemaname,tablename from pg_tables where schemaname = ('public') order by 1 desc;ERROR: relation "public.table_name_with_double_quota" does not exist as it has many tables that were created with double quotes e.g. public."table_name_with_double_quota" we tried dropping some but it fails with other.gpadmin=# select oid, * from pg_namespace where nspname = 'public';
oid | nspname | nspowner | nspacl
------+---------+----------+----------------------------------
2200 | public | 10 | {gpadmin=UC/gpadmin,=UC/gpadmin}
(1 row)
Time: 1.137 msselect x.size, pg_size_pretty(x.size) from (select pg_relation_size(oid) as size from pg_class where relnamespace = <schema_oid>) x;
select
x.size,
pg_size_pretty(x.size)
from
(
select
pg_relation_size(oid) as size
from
pg_class
where
relnamespace = (select oid from pg_namespace where nspname = 'public')
) x;
GP Hackathon update!
We’re about 3 weeks away from the GP Hackathon!
) :
The Greenplum Hackathon is officially 2 weeks away! 
Hack project sign up is open from now until July 27 (official start of the Hackathon). Please add your hack idea to this page! Check out the ideas already submitted, and if one interests you, add your name in the “Interested in Joining” column.
If you proposed a hack project idea, start thinking about building your team. We encourage teams of 3-5 people, and cross-geo teams means more hacking time! Consider folks and roles outside of your direct team as well.
Block your calendars for July 27-28 if you plan to participate! Start moving meetings as necessary so you can focus on your team’s hack project. Calendar invites for the two watch parties on Friday, July 29 will be sent out today. Please plan to join the one in your local time to watch all the hack project recordings!
The Hackathon Planning Committee is updating the Hacker Guidelines regularly. Please check it out to learn more about the event.
Want to coordinate with fellow hackers but aren’t sure where to go? Join the #gp-hackathon channel and kickstart the conversation there!
VMware Tanzu™ Greenplum® 5.29.7 release is available to "All Users" on TanzuNet.
We’re only a few days away from the Virtual Greenplum Hackathon! There’s still time to sign up here with your own idea, join a team, or pick one of the many ideas already listed (there are currently 34 ideas!) and join in on the fun.To help you prepare for the event, please check out the following pages:https://docs.vmware.com/en/VMware-Tanzu-Greenplum/6/greenplum-database/GUID-deploying-using-ova.html?hWord=N4IghgNiBcIPYDcwgL5A with: https://network.tanzu.vmware.com/products/vmware-tanzu-greenplum#/releases/1112714/file_groups/8832

The Virtual Greenplum Hackathon officially starts TODAY!
You will have the following 2 days to hack and record your demo video. The maximum time for the recorded demos is 5 minutes. Please keep in mind the deadline to submit your demo video into the SharePoint Folder is 8:00 PM your local time on Thursday, July 28.Make sure your video includes the following:Currently we are using the Greenplum Version of 4.3.33 Having Infra of 10 Cisco Servers ( 1 master , 1 standby, 8 Segments host ). Currently we are having 40+ TB's of data on Single Cluster. As required we are upgrading the database to GP 6+ Version as well as Server also ( infra ). Could you please suggest if you have any tested HP Server details ( Configuration ) Which could handle the GP Version 6.x.x as well as 40TB's of data smoothly. Also we are having constrain from storage side to use Existing External NVME Storage with New Server. Please let us know if VMware Greenplum support it or any existing system which is using the External NVME Storage.
sdw3|7000|/data1mirror/mirror/gpseg32 sdw3n|7000|/data1mirror/mirror/gpseg32
2022-06-11 18:35:02.072405 CEST,,,p24272,th2041849984,,,,0,,,seg36,,,,,"ERROR","XX000","could not connect to the primary server: could not translate host nam e ""sdw3n"" to address: Name or service not known
34 | 32 | p | p | s | u | 6000 | XEMDUB07DBXP740 | sdw2 | /data1/primary/gpseg32 98 | 32 | m | m | s | u | 7000 | XEMDUB07DBXP750 | sdw3 | /data1mirror/mirror/gpseg32
hostname -s, but used hostname. Our issue is the hostname append domain like .local, so mdw became mdw.local, but we only want the short name, e.g. mdw.Furthermore, we need a reliable way to get the alias of a given host, instead of rely on the hostname for all the GP internal communications.That probably goes really deeper on the CM code base.I will let the CM team comments more on it. @Sumit BasumallickPlease help list one or two compensating control for not implementing pgCrypto (Data at Rest Encryption) for our Greenplum appliance. The decision not to implement includes performance impact and not having enough reource/downtime.
VMware Tanzu™ Greenplum® Command Center 6.8.0 release is available to "All Users" on TanzuNet.vacuum verbose might provide additional cluespsql through a terminal? If so then they could nohup it.statement_timeout , or something totally differentOutput results:
bqr=# select name, setting from pg_settings where name like '%timeout';
name | setting
--------------------------------------------+---------
authentication_timeout | 60
deadlock_timeout | 1000
gp_connection_send_timeout | 3600
gp_email_connect_timeout | 15
gp_fts_probe_timeout | 20
gp_interconnect_min_retries_before_timeout | 100
gp_interconnect_setup_timeout | 7200
gp_interconnect_transmit_timeout | 3600
gp_segment_connect_timeout | 600
gp_vmem_idle_resource_timeout | 18000
readable_external_table_timeout | 0
replication_timeout | 60000
statement_timeout | 0
(13 rows)
bqr=#Does gpss handles all the charactersets by default or it should be explicitly set by job [eg: ascii , utf8 , kanji etc
select cliente, salaprincipal from (
select tb_cliid cliente, salnombre salaprincipal
, sum(tb_coinin)/100.0 as CoinIn
, rank() over (partition by tb_cliid order by CoinIn desc) as salarank
, row_number() over (partition by tb_cliid order by coinin desc) aux
from tebs..sesionescliente a
inner join tebs..salas b on a.salasid = b.salasid
left join tebs..m60 m on a.fecha = m.fecha and a.tb_idteb = m.islotid
where tb_fechas>tb_fechae and tb_fechae>'00010101'
and a.FECHA BETWEEN '2022-01-01' and '2022-01-31'
VMware Tanzu™ Greenplum® 6.21.2 release is available to "All Users" on TanzuNet.
https://www.linkedin.com/feed/update/urn:li:activity:6970478866842755073/SELECT * from table01 (table.txt)CREATE OR REPLACE FUNCTION Inner_function()
RETURNS integer
LANGUAGE plpgsql AS
$func$
DECLARE
a int;
BEGIN
select count(*) into a from t as b, t as c;
return a;
END
$func$;
gpadmin=# explain select * from Inner_function();
QUERY PLAN
--------------------------------------------------
Result (cost=0.26..0.27 rows=1 width=0)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.26 rows=1 width=0)
Optimizer: Postgres query optimizer
(4 rows)auto_explain, gpdb source code contains it.However, the code has a bug cannot be used now. The good news is we have a PR from community to fix this: https://github.com/greenplum-db/gpdb/pull/13846I have looked at it, seems reasonable. I’ve already pinged team to deep review.With the PR and 6X (without), it shoud works. The GUC is to print plan in function. set auto_explain.log_nested_statements = 1;Refer pg’s doc: https://www.postgresql.org/docs/current/auto-explain.htmlAlter table <table_name> add column <column_name> numeric(16,4) encoding (compresstype=zlib, compresslevel=5); Alter table <table_name> add column <column_1> numeric(16,4) encoding (compresstype=zlib, compresslevel=5), <column_2>......<column_29> numeric(16,4) encoding (compresstype=zlib, compresslevel=5) ;
ssl_ciphers https://docs.vmware.com/en/VMware-Tanzu-Greenplum/6/greenplum-database/GUID-security-guide-topics-Authenticate.html
https://www.linkedin.com/feed/update/urn:li:share:6976616520466472960/
. That might be the only way though as far as I can tell
[Directed release]
VMware Tanzu™ Greenplum® Command Center 6.8.2 release is available to "All Users" on TanzuNet.
Check out the latest Greenplum Drumbeat on Confluence with program, product, customer, and team updates!external web table execute, looked at the docs can’t seems to find any mention of it.I can’t seems to find any reason to why this command wont work for eg.s here is a trimmed down script of a larger script-- External table with simple top command and greping the things I like
gpadmin=# drop external table if exists dba_work._ps_m;
NOTICE: table "_ps_m" does not exist, skipping
DROP EXTERNAL TABLE
gpadmin=# create external web table dba_work._ps_m( output text )
gpadmin-# execute 'top -bc -n1 -u gpadmin | grep -i ''[p]ostgres.*con[0-9].*'' ' on master
gpadmin-# format 'TEXT' (DELIMITER '!' NULL '')
gpadmin-# ;
CREATE EXTERNAL TABLE
-- Fails with the below error ( seems to come from the grep ) , not sure what the issue is
gpadmin=# select * from dba_work._ps_m;
ERROR: external table _ps_m command ended with error.
DETAIL: Command: execute:top -bc -n1 -u gpadmin | grep -i '[p]ostgres.*con[0-9].*'
-- there is no error on the master log, it only shows it had a exception
/data/master/gp_6.22.0_20220929131207-1/pg_log/gpdb-2022-10-13_000000.csv:2022-10-13 07:56:30.786451 UTC,"gpadmin","gpadmin",p26000,th-641820544,"[local]",,2022-10-13 07:56:21 UTC,0,con59504,cmd4,seg-1,,,,sx1,"ERROR","38000","external table _ps_m command ended with error.","Command: execute:top -bc -n1 -u gpadmin | grep -i '[p]ostgres.*con[0-9].*'",,,,,"select * from dba_work._ps_m;",0,,"url_execute.c",354,
/data/master/gp_6.22.0_20220929131207-1/pg_log/gpdb-2022-10-13_000000.csv:2022-10-13 07:56:30.786531 UTC,"gpadmin","gpadmin",p26000,th-641820544,"[local]",,2022-10-13 07:56:21 UTC,0,con59504,cmd4,seg-1,,,,sx1,"LOG","00000","An exception was encountered during the execution of statement: select * from dba_work._ps_m;",,,,,,"select * from dba_work._ps_m;",0,,"elog.c",2116,
-- Executing the command on the unix prompt directly shows its indeed a valid command
gpadmin=# \! top -bc -n1 -u gpadmin | grep -i '[p]ostgres.*con[0-9].*'
5218 gpadmin 20 0 561172 22396 13496 S 0.0 0.6 0:02.24 postgres: 3005, gpadmin gpadmin [local] con48431 cmd6 idle
18708 gpadmin 20 0 578560 59280 21672 S 0.0 1.5 0:18.57 postgres: 3005, gpmon gpperfmon 192.168.99.100(59612) con33 cmd13198 idle
18710 gpadmin 39 19 598180 28668 19748 S 0.0 0.7 0:18.02 postgres: 30010, gpmon gpperfmon 192.168.99.100(51372) con33 seg0 idle
18711 gpadmin 39 19 598180 27864 19784 S 0.0 0.7 0:17.99 postgres: 30011, gpmon gpperfmon 192.168.99.100(56198) con33 seg1 idle
25827 gpadmin 20 0 601544 43592 21200 S 0.0 1.1 0:00.23 postgres: 3005, gpmon gpperfmon 192.168.99.100(43362) con59484 cmd210 idle
25831 gpadmin 39 19 604660 21712 13048 S 0.0 0.6 0:00.01 postgres: 30010, gpmon gpperfmon 192.168.99.100(35124) con59484 seg0 idle
25832 gpadmin 39 19 596544 20772 13180 S 0.0 0.5 0:00.02 postgres: 30011, gpmon gpperfmon 192.168.99.100(39950) con59484 seg1 idle
25859 gpadmin 39 19 546376 13400 5812 S 0.0 0.3 0:00.00 postgres: 3005, gpmon gpperfmon [local] con59484 seg-1 idle
25918 gpadmin 20 0 546628 18068 10008 S 0.0 0.5 0:00.00 postgres: 3005, gpmon gpperfmon 192.168.99.100(43400) con59495 cmd8 idle
25922 gpadmin 20 0 596088 16600 9412 S 0.0 0.4 0:00.00 postgres: 30010, gpmon gpperfmon 192.168.99.100(35162) con59495 seg0 idle
25923 gpadmin 20 0 596088 16584 9400 S 0.0 0.4 0:00.00 postgres: 30011, gpmon gpperfmon 192.168.99.100(39988) con59495 seg1 idle
26000 gpadmin 20 0 549628 24196 15552 S 0.0 0.6 0:00.01 postgres: 3005, gpadmin gpadmin [local] con59504 cmd4 idle
26002 gpadmin 39 19 596248 20228 12904 S 0.0 0.5 0:00.00 postgres: 30010, gpadmin gpadmin 192.168.99.100(35192) con59504 seg0 idle
26003 gpadmin 39 19 596248 20328 13004 S 0.0 0.5 0:00.00 postgres: 30011, gpadmin gpadmin 192.168.99.100(40018) con59504 seg1 idle
26087 gpadmin 39 19 596360 12844 5436 S 0.0 0.3 0:00.00 postgres: 30011, gpmon gpperfmon 192.168.99.100(40050) con33 seg1 idle
-- Removing the grep works
gpadmin=#
gpadmin=# drop external table if exists dba_work._ps_m;
DROP EXTERNAL TABLE
gpadmin=# create external web table dba_work._ps_m( output text )
gpadmin-# execute 'top -bc -n1 -u gpadmin ' on master
gpadmin-# format 'TEXT' (DELIMITER '!' NULL '')
gpadmin-# ;
CREATE EXTERNAL TABLE
gpadmin=# select * from dba_work._ps_m;
output
---------------------------------------------------------------------------------
top - 07:57:01 up 2 days, 18:42, 1 user, load average: 0.39, 0.26, 0.15
Tasks: 171 total, 1 running, 168 sleeping, 2 stopped, 0 zombie
%Cpu(s): 0.0 us, 3.2 sy, 0.0 ni, 96.8 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 3880984 total, 152344 free, 388628 used, 3340012 buff/cache
KiB Swap: 2097148 total, 2090740 free, 6408 used. 2461888 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1921 gpadmin 20 0 597232 40492 21068 S 0.0 1.0 0:00.14 postgres: +
1924 gpadmin 39 19 604660 21664 13000 S 0.0 0.6 0:00.01 postgres: +
1925 gpadmin 39 19 596496 20724 13172 S 0.0 0.5 0:00.01 postgres: +
2001 gpadmin 39 19 546376 13296 5760 S 0.0 0.3 0:00.00 postgres: +
-- Adding it on file ends up with the same error
gpadmin=# \!cat cmm.sh
#!/bin/sh
top -bc -n1 -u gpadmin | grep -i '[p]ostgres.*con[0-9].*'
gpadmin=#
gpadmin=#
gpadmin=# drop external table if exists dba_work._ps;
DROP EXTERNAL TABLE
gpadmin=# create external web table dba_work._ps( output text )
gpadmin-# execute '/home/gpadmin/cmm.sh' on master
gpadmin-# format 'TEXT' (NULL '')
gpadmin-# ;
CREATE EXTERNAL TABLE
gpadmin=#
gpadmin=#
gpadmin=# select * from dba_work._ps;
ERROR: external table _ps command ended with error.
DETAIL: Command: execute:/home/gpadmin/cmm.sh
gpadmin=#
-- Echo'ng stuff before and end of the command seems to suppress any error
gpadmin=# \!cat cmm.sh
#!/bin/sh
echo "start"
top -bc -n1 -u gpadmin | grep -i '[p]ostgres.*con[0-9].*'
echo "end"
gpadmin=# select * from dba_work._ps;
output
--------
start
end
(2 rows)The problem is that you can’t analyze external table so you can’t really use S3 as a working environment that would replace your local storage.
gppylib/gplog.py
99: - Logs output to a file, typically in ~/gpAdminLogs
283: - if logdir is not specified it uses ~/gpAdminLogs
295: gpadmin_logs_dir = home_dir + "/gpAdminLogs"
➜ bin git:(e5309e047a) ✗ vim gppylib/gplog.py
➜ bin git:(e5309e047a) ✗ ack _enable_gpadmin_logging
gppylib/gplog.py
109: _enable_gpadmin_logging(appName, logdir)
280:def _enable_gpadmin_logging(name, logdir=None):
➜ bin git:(e5309e047a) ✗ vim gppylib/gplog.py
➜ bin git:(e5309e047a) ✗ ack setup_tool_logging
gpinitstandby
776: setup_tool_logging(EXECNAME,unix.getLocalHostname(),unix.getUserName())
gpmovemirrors
314: setup_tool_logging(appName=EXECNAME
gpcheckcat
54:gplog.setup_tool_logging(EXECNAME, getLocalHostname(), getUserName())
lib/gpconfigurenewsegment
344: logger = gplog.setup_tool_logging(EXECNAME, unix.getLocalHostname(), unix.getUserName(),
gppylib/test/unit/test_unit_gpsegrecovery.py
337: @patch('recovery_base.gplog.setup_tool_logging')
gppylib/test/unit/test_unit_gpsegsetuprecovery.py
292: @patch('recovery_base.gplog.setup_tool_logging')
gppylib/test/unit/test_unit_recovery_base.py
19: patch('recovery_base.gplog.setup_tool_logging', return_value=self.mock_logger),
29: self.mock_setup_tool_logging = self.get_mock_from_apply_patch('setup_tool_logging')
178: self.mock_setup_tool_logging.call_args_list)
193: self.mock_setup_tool_logging.call_args_list)
gppylib/util/test/test_logging.py
11:gplog.setup_tool_logging("test_logging", "localhost", "gpadmin")
gppylib/mainUtils.py
324: gplog.setup_tool_logging(execname, hostname, username,
gppylib/gplog.py
16: logger = gplog.setup_tool_logging(EXECNAME, hostname, username, logdir)
95:def setup_tool_logging(appName, hostname, userName, logdir=None, nonuser=False):
112: # now reset the default formatter (someone may have called get_default_logger before calling setup_tool_logging)
gpconfig
45:setup_tool_logging(EXECNAME, getLocalHostname(), getUserName())
gpactivatestandby
31: from gppylib.gplog import get_default_logger, setup_tool_logging, enable_verbose_logging, get_logger_if_verbose
347:setup_tool_logging(EXECNAME, unix.getLocalHostname(), unix.getUserName())
354: setup_tool_logging(EXECNAME, unix.getLocalHostname(), unix.getUserName(), logdir=options_.logfile)
gpexpand
2414: setup_tool_logging(EXECNAME, getLocalHostname(), getUserName())
gpdeletesystem
292: setup_tool_logging(EXECNAME, unix.getLocalHostname(), unix.getUserName())
299: setup_tool_logging(EXECNAME, unix.getLocalHostname(), unix.getUserName(), logdir=options.logfile)~/gpAdminLogs.dbeaver , that is a bug of DBeaver 22.2, "DBeaver doesn't send the proper client app name for Greenplum driver", they will correct it on the next release 22.3(should be released on 12th December 2022).log_line_prefix
8 months ago6.20.1.gpexpand.The performance of gpexpand computation of table size has been improved.
ERRORDATA_STACK_SIZE (MPP-32506) raised recently by another customer. 2) May i know in what circumstance customer will likely hit this error on their current version?This customer has ~ 450TB data volume with 90% partition tables. The customer is not really keen on doing minor upgrade due to time constraint without a strong reason.we would like to know if it is ok to stay with this version for gpexpand.6.19.1In short:According to the GP release note, in GP6.22.0, there is a performance improvement as documented below. 1) Customer is keen on knowing how much improvement it will gain if they upgrade to GP6.22.x.
gpepxand does not run with --simple then, it will need to compute each table’s size. The above PR only enhance this part of computation.In short: if run gpexpand with --simple-progress, nothing improve; if run without, gpexpand info populate is speed up.I have a local test for it:We have done local test to show the performance gain. In a local 8*8
64-segments cluster, we create 100 partition tables, each partition
table contains 2 middle level partitions, each middle level partition
contains 365 leaf partitions. Then in this database, we compare the
speed of the old populate SQL (evaluate pg_relation_size on QD via
dispatching, a tuple a dispatch) and the new SQL (a single SQL and only
need to dispatch once). This patch’s SQL only takes about 3.8 sec, however,
the old SQL takes about 50 sec.
In addition, I have seen an PANIC error ERRORDATA_STACK_SIZE (MPP-32506) raised recently by another customer. 2) May i know in what circumstance customer will likely hit this error on their current version?
8 months ago
20221114:16:09:17:032384 gpcheckcat:l91fpgm1:gpadmin-[INFO]:-[FAIL] pg_database <=> filesystem
20221114:16:09:17:032384 gpcheckcat:l91fpgm1:gpadmin-[ERROR]:-pg_database <=> filesystem found 4 issue(s)
20221114:16:09:17:032384 gpcheckcat:l91fpgm1:gpadmin-[ERROR]:-
SELECT tablespace_oid, database_oid, count(*)
FROM gp_persistent_relation_node_check() p
LEFT OUTER JOIN pg_database d
ON (p.database_oid = d.oid)
WHERE d.oid is null and database_oid != 0
GROUP BY tablespace_oid, database_oid;
20221114:16:09:17:032384 gpcheckcat:l91fpgm1:gpadmin-[ERROR]:---------
20221114:16:09:17:032384 gpcheckcat:l91fpgm1:gpadmin-[ERROR]:-l91fpgs7.tcsb.admin.ch:2004:/data2/primary/gpseg52
20221114:16:09:17:032384 gpcheckcat:l91fpgm1:gpadmin-[ERROR]:- tablespace_oid | database_oid | count
20221114:16:09:17:032384 gpcheckcat:l91fpgm1:gpadmin-[ERROR]:- 1663 | 87124073 | 5
20221114:16:09:17:032384 gpcheckcat:l91fpgm1:gpadmin-[ERROR]:---------
20221114:16:09:17:032384 gpcheckcat:l91fpgm1:gpadmin-[ERROR]:-l91fpgs7.tcsb.admin.ch:2005:/data2/primary/gpseg53
20221114:16:09:17:032384 gpcheckcat:l91fpgm1:gpadmin-[ERROR]:- tablespace_oid | database_oid | count
20221114:16:09:17:032384 gpcheckcat:l91fpgm1:gpadmin-[ERROR]:- 1663 | 87124073 | 1
VMware Tanzu™ Greenplum® 6.22.2 release is available to "All Users" on TanzuNet.We upgraded to gpdb 6.21.3. And I changed the gp_interconnect_address_type to unicast. But I'm not sure that network performance has improved. Which value of gp_interconnect_address_type in gpdb is better unicast or wilcard?
When this parameter is set to unicast, Greenplum Database uses the gp_segment_configuration.address field to perform address binding. This reduces port usage on segment hosts and prevents interconnect traffic from being routed through unintended (and possibly slower) network interfaces. When this parameter is set to wildcard, Greenplum Database uses a wildcard address for binding, enabling the use of any network interface compliant with routing rules. NOTE: In some cases, inter-segment communication using the unicast strategy may not be possible. One example is if the source segment's address field and the destination segment's address field are on different subnets and/or existing routing rules do not allow for such communication. In these cases, you must configure this parameter to use a wildcard address for address binding.
we will have to create temporarily on the same cluster a GP6 instance alongside the GP4 instance.
bradfordb-a01.vmware.com) but my hostname has been changed to bradfordbXXXXX.vmware.com. The hostname changed occurred while the cluster was not running. Is there a way to update my cluster to use the new hostname?I found this KB article that uses gprecoverseg but it looks like that assumes a running cluster.gpstart -a now. Haven’t tried any queries yet.
VMware Tanzu™ Greenplum® Command Center 6.8.3 release is available to "All Users" on TanzuNet.
VMware Tanzu™ Greenplum® Data Copy Utility 2.4.0 release is available to "All Users" on TanzuNet.
VMware Tanzu™ Greenplum® Backup and Restore 1.27.0 release is available to "All Users" on TanzuNet.fiat=# alter table dg_test.tl_crm_adobe_mkt ADD PARTITION START ('2022-03-02'::date) END ('2023-01-01'::date) EVERY ('1 day'::interval) WITH (orientation='row', appendonly='true', compresstype=zlib, compresslevel='5');
ERROR: cannot specify EVERY when adding RANGE partition to relation "tl_crm_adobe_mkt"
VMware Tanzu™ Greenplum® 7.0.0-beta.0 release is available to "All Users" on TanzuNet.demo=# select * from t_json;
json_val
------------------------------------------------------------------------------------------------------------------------------------------------------------
{"caac":"1","lcmp1":null,"lcrt":"Sans AAC","llon":"Sans AAC","l30":null,"dhchg":"2023-01-04T17:50:42.150845-05:00"}
{"caac":"3","lcmp1":null,"lcrt":"AAC MAIF PP post2007","llon":"AAC MAIF","l30":null,"dhchg":"2023-01-04T17:50:42.150845-05:00"}
{"caac":"4","lcmp1":null,"lcrt":"AAC Autre société","llon":"AAC Autre société","l30":null,"dhchg":"2023-01-04T17:50:42.150845-05:00"}
{"caac":"0","lcmp1":"1","lcrt":"AAC MAIF PP avt 2007","llon":"AAC MAIF débuté avant le 01/01/2007","l30":"1","dhchg":"2023-01-04T17:50:42.150845-05:00"}
{"caac":"","lcmp1":null,"lcrt":"Sans information","llon":"Sans information","l30":null,"dhchg":"2023-01-04T17:50:42.150845-05:00"}
{"caac":"2","lcmp1":null,"lcrt":"AAC MAIF PP avt 2007","llon":"AAC MAIF débuté avant le 01/01/2007","l30":null,"dhchg":"2023-01-04T17:50:42.150845-05:00"}
(6 rows)
demo=# select v.* from t_json, json_populate_record(null::t_json_record,t_json.json_val) v;
caac | lcmp1 | lcrt | llon | l30 | dhchg
------+-------+----------------------+-------------------------------------+-----+-------------------------------
1 | | Sans AAC | Sans AAC | | 2023-01-04 17:50:42.150845-05
0 | 1 | AAC MAIF PP avt 2007 | AAC MAIF débuté avant le 01/01/2007 | 1 | 2023-01-04 17:50:42.150845-05
| | Sans information | Sans information | | 2023-01-04 17:50:42.150845-05
2 | | AAC MAIF PP avt 2007 | AAC MAIF débuté avant le 01/01/2007 | | 2023-01-04 17:50:42.150845-05
4 | | AAC Autre société | AAC Autre société | | 2023-01-04 17:50:42.150845-05
3 | | AAC MAIF PP post2007 | AAC MAIF | | 2023-01-04 17:50:42.150845-05
(6 rows)demo=# explain select v.* from t_json, json_populate_record(null::t_json_record,t_json.json_val) v;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=10000000000.00..10000000004.30 rows=6 width=168)
-> Gather Motion 16:1 (slice1; segments: 16) (cost=0.00..4.18 rows=6 width=140)
-> Seq Scan on t_json (cost=0.00..4.06 rows=1 width=140)
-> Function Scan on json_populate_record v (cost=0.00..0.01 rows=1 width=168)
Optimizer: Postgres query optimizer
(5 rows)The function pg_column_size displays bytes required to store the value, possibly with TOAST compression.
select
(segment_file_num/128)+1 as column_number,
pg_size_pretty(sum(mirror_append_only_new_eof)::bigint) as disk_size,
(sum(sum(mirror_append_only_new_eof)::bigint) over ())/1024^3 as total_size_GB
from gp_dist_random('gp_persistent_relation_node') a
, (select relfilenode
FROM pg_class c
, pg_namespace n
WHERE n.oid = c.relnamespace
AND c.relname NOT LIKE '%_err'
AND c.relname like 'historicodaf_all%prt%'
AND c.relkind = 'r') parts
where relfilenode_oid = parts.relfilenode
group by 1
order by 1;pg_column_size() gives per column size for tuple. AO doesn't compress single column, it compresses multiple columns in a block. So, pg_column_size() is providing the uncompressed size and understand its little misleading in AO context due to compression. But there is not way to get single column compression value as we don't compress single column but bulk batch of it.We do have plan to provide compression_ratio at column level granularity instead of only table level currently.the SQL posted by Luis will help only for GPDB5 and lower. Not for GPDB6 or higher.One can look into pg_aoseg EOF values and get the result currently which is complicated SQL. Let me try to provide one.select
val[5]::int % 127 as column,
sum(val[7]::int) as eof,
sum(val[8]::int) as eof_uncompressed,
(
1 - (sum(val[7]::decimal) / sum(val[8]::decimal))
)
*100 as reduction_percentage
from
(
select
string_to_array(gp_toolkit.__gp_aocsseg('<table_name_here>'::regclass::oid)::text, ',') as
values
from
gp_dist_random('gp_id')
)
as dt(val)
group by
val[5]
order by
val[5];
) and since they don't have support they've hired professional services to help. They currently have 46 segments down on 3 nodes in their production system and those 46 segments have been down since October 8th 2022 (it's a miracle the system is still available and they didn't have a double fault). I've been working with them the past 2 weeks or so trying to nail down what's going on and I feel I'm pretty close but don't know what to make of it. Basically when trying to recover the segments with gprecoverseg, all of the downed segments fail to startup and when I look at the startup logs for all of those segments, they all have the same error2023-01-10 21:10:43.741047 CST,,,p601,th-630814816,,,,0,,,seg-1,,,,,"LOG","XX000","could not create IPv6 socket: Address family not supported by protocol (pqcomm.c:381)",,,,,,,,"StreamServerPort","pqcomm.c",381,2023-01-10 21:10:43.856942 CST,,,p601,th-630814816,,,,0,,,seg-1,,,,,"LOG","00000","temporary files using default filespace",,,,,,,,"primaryMirrorPopulateFilespaceInfo","primary_mirror_mode.c",2501,2023-01-10 21:10:43.857190 CST,,,p601,th-630814816,,,,0,,,seg-1,,,,,"LOG","00000","transaction files using default pg_system filespace",,,,,,,,"primaryMirrorPopulateFilespaceInfo","primary_mirror_mode.c",2561,Their /etc/hosts isn't referring to IPV6 addresses for anything and when I use traceroute all of the entries in the catalog resolve to ipv4 addresses. I can't find anything specifically in the GPDB settings that specifies the use of IPV4 or IPV6 and I'm a bit stuck at the moment with this. I'm able to reproduce this issue on their environment by running netcat like nc -l 40001 -6 (the -6 specifies IPV6) and I get the same error, but when I bind to a TCP socket via IPV4 with the same command it works fine. Is there something I'm missing here or maybe someone with some ancient 4.2 knowledge might know of a bug that existed?For what it's worth, I had them collect all of their change requests from the past year and send them to me and the only thing that might be related was some networking changes that happened in April of 2022, however they bounced the database every weekend and the segments have been down since October 2022 so I'd be surprised if those 2 things are related, however as we all know most issues occur when changes happen in a system (edited)
We’re looking for Greenplum 7 Beta Program volunteers! Greenplum will be shipping a series of Beta releases, separated by about 6 weeks each, with a target GA by mid-2023. Please see the GP7 Beta Plan on Confluence for more details. Beta.1 is on track to GA in early February. If you are an internal technical engineer and have time to dedicate to testing, we encourage you to participate in the internal Greenplum 7 Beta Program!
The program will consist of internal volunteers for Beta.1 and Beta.2. We will hold a series of meetings on Zoom to structure testing and gather feedback from participants. If you are interested in participating in the Greenplum 7 Beta Program, please let me (Kristine Scott) know over Slack or email.WAL_LEVEL=replica. So, if using appendoptimized tables then the benefit is huge as disk IO and n/w bandwidth will be lower for loads and I/U/D for these tables.
Greenplum v.7 GA Field Enablement
1 (no cache) to 20 to increase the performance of insert operations on tables that are defined with a serial data type using a sequence value.NOTE
When operating with a cache of sequence numbers, Greenplum Database may discard some cached sequence values. If you require consecutive values, you must explicitly setCACHE 1when you create the sequence.
demo=# create sequence t_seq2;
CREATE SEQUENCE
demo=# \d+ t_seq2
Sequence "public.t_seq2"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 20demo=# create table t_seq (id serial, b text);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
demo=# \d+ t_seq
Table "public.t_seq"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+-----------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('t_seq_id_seq'::regclass) | plain | |
b | text | | | | extended | |
Distributed by: (id)
Access method: heap
demo=# \d+ t_seq_id_seq
Sequence "public.t_seq_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
integer | 1 | 1 | 2147483647 | 1 | no | 1
Owned by: public.t_seq.idJIT: Options: Inlining true, Optimization true, Expressions true, Deforming true. (slice0): Functions: 154.00. Timing: 946.929 ms total. (slice1): Functions: 47.00 avg x 6 workers, 47.00 max (seg0). Timing: 671.129 ms avg x 6 workers, 792.210 ms max (seg4). (slice2): Functions: 3.00. Timing: 3311.791 ms total. (slice3): Functions: 446.00 avg x 6 workers, 446.00 max (seg0). Timing: 190781.446 ms avg x 6 workers, 194534.343 ms max (seg2). (slice4): Functions: 3.00. Timing: 3308.925 ms total. (slice5): Functions: 446.00 avg x 6 workers, 446.00 max (seg0). Timing: 187603.358 ms avg x 6 workers, 195787.833 ms max (seg5). (slice6): Functions: 3.00. Timing: 3208.833 ms total. (slice7): Functions: 446.00 avg x 6 workers, 446.00 max (seg0). Timing: 194674.867 ms avg x 6 workers, 203777.035 ms max (seg1). (slice8): Functions: 3.00. Timing: 2985.731 ms total. (slice9): Functions: 446.00 avg x 6 workers, 446.00 max (seg0). Timing: 187902.543 ms avg x 6 workers, 192896.583 ms max (seg3). (slice10): Functions: 3.00. Timing: 3412.365 ms total. (slice11): Functions: 446.00 avg x 6 workers, 446.00 max (seg0). Timing: 188254.708 ms avg x 6 workers, 190556.917 ms max (seg3). (slice12): Functions: 3.00. Timing: 2908.909 ms total. (slice13): Functions: 446.00 avg x 6 workers, 446.00 max (seg0). Timing: 192885.233 ms avg x 6 workers, 197653.726 ms max (seg4). (slice14): Functions: 3.00. Timing: 2897.923 ms total. (slice15): Functions: 446.00 avg x 6 workers, 446.00 max (seg0). Timing: 191093.726 ms avg x 6 workers, 198085.590 ms max (seg4). (slice16): Functions: 3.00. Timing: 3174.595 ms total. (slice17): Functions: 446.00 avg x 6 workers, 446.00 max (seg0). Timing: 188828.194 ms avg x 6 workers, 194991.944 ms max (seg3). (slice18): Functions: 3.00. Timing: 3408.463 ms total. (slice19): Functions: 446.00 avg x 6 workers, 446.00 max (seg0). Timing: 191293.965 ms avg x 6 workers, 197312.191 ms max (seg5). (slice20): Functions: 3.00. Timing: 2485.455 ms total. (slice21): Functions: 446.00 avg x 6 workers, 446.00 max (seg0). Timing: 189771.192 ms avg x 6 workers, 196938.645 ms max (seg3). (slice22): Functions: 3.00. Timing: 3073.320 ms total. (slice23): Functions: 446.00 avg x 6 workers, 446.00 max (seg0). Timing: 192214.476 ms avg x 6 workers, 194280.460 ms max (seg5). (slice24): Functions: 3.00. Timing: 3845.382 ms total. (slice25): Functions: 446.00 avg x 6 workers, 446.00 max (seg0). Timing: 189134.128 ms avg x 6 workers, 198812.990 ms max (seg1). (slice26): Functions: 3.00. Timing: 2563.702 ms total. (slice27): Functions: 446.00 avg x 6 workers, 446.00 max (seg0). Timing: 189432.891 ms avg x 6 workers, 193443.127 ms max (seg5). (slice28): Functions: 3.00. Timing: 3199.523 ms total. (slice29): Functions: 446.00 avg x 6 workers, 446.00 max (seg0). Timing: 189337.987 ms avg x 6 workers, 194125.168 ms max (seg4). (slice30): Functions: 3.00. Timing: 3469.361 ms total. (slice31): Functions: 446.00 avg x 6 workers, 446.00 max (seg0). Timing: 193889.087 ms avg x 6 workers, 196561.295 ms max (seg3).
also planner and ORCA have different cost models, it will be hard to set it properlly as we don’t know when each will be used
SELECT current_timestamp AS tms, n.nspname AS schema_name, c.relname AS table_name, c.reloptions as table_options, c.relhassubclass as is_partitioned FROM pg_class c LEFT JOIN pg_appendonly a ON c.oid = a.relid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE (n.nspname <> ALL (ARRAY['information_schema'::name, 'pg_catalog'::name, 'pg_toast'::name, 'gp_toolkit'::name])) --AND c.relkind = 'r'::"char" and c.relname not like '%_1_prt_%' and n.nspname = 'tpcds' order by 3 ;
postgres=# create table ao2heap(a int, b int) with(appendonly,compresstype=zlib) partition by range(a) (partition p1 start(0) end(1));
CREATE TABLE
postgres=# alter table ao2heap set access method heap;
ALTER TABLE
postgres=# \d+ ao2heap
<setting use_container_logging="yes"/> to you runtime, such as:<?xml version="1.0" ?>
<configuration>
<runtime>
<id>plc_python_shared</id>
<image>pivotaldata/plcontainer_python_shared:devel</image>
<command>/clientdir/pyclient.sh</command>
<shared_directory access="ro" container="/clientdir" host="/home/mt/workstage/install/gpdb-6x/bin/plcontainer_clients"/>
<setting use_container_logging="yes"/>
</runtime>
</configuration>docker ps -a simultaneously, you can see a container started and exit after several seconds, you need copy CONTAINER ID and use docker logs {id} to check the logs of container.I suspect that the python 3.9 doesn't match the py3client of plcontainer./clientdir mount
VMware Tanzu™ Greenplum® 6.23.2 release is available to "All Users" on TanzuNet.
VMware Tanzu™ Greenplum® 6.23.2 release is available to "All Users" on TanzuNet.
4 months agoERROR: addressing partition by RANK is no longer supported_1_prt_ for its child partitions whereas this might not be true any more with the new way of creating partition in GP7. Example will be attached in my replies in this threadenable_partition_pruning only applied to Postgres Planner, not GPORCA? In my testing, whether enable_partition_pruning is ON or OFF, it seems GPORCA will only scan the filtered partition(s) anyway.
4 months agoDROP TABLE IF EXISTS public.test_partition1_gp7 CASCADE; CREATE TABLE public.test_partition1_gp7 ( trans_id SERIAL, sales_date date, sales_amount decimal(9,2) ) DISTRIBUTED BY (trans_id) PARTITION BY RANGE (sales_date) (START (date '2021-01-01') INCLUSIVE END (date '2022-01-01') EXCLUSIVE EVERY (INTERVAL '1 month') ); -- try to drop partition test_partition9_gp6_1_prt_1 FOR '2021-01-01' ALTER TABLE public.test_partition9_gp6 DROP PARTITION FOR (RANK(1));
ERROR: addressing partition by RANK is no longer supported
LINE 1: ...BLE public.test_partition9_gp6 DROP PARTITION FOR (RANK(1));
^
HINT: Use partition name or FOR (<partition key value>) instead
4 months agoDROP TABLE IF EXISTS public.test_partition1_gp7 CASCADE;
-- New syntax to create a partition table and its child partitions
CREATE TABLE public.test_partition1_gp7
(
trans_id SERIAL,
sales_date date,
sales_amount decimal(9,2)
)
DISTRIBUTED BY (trans_id)
PARTITION BY RANGE (sales_date)
;
CREATE TABLE public.test_partition1_gp7_m2021_01 PARTITION OF public.test_partition1_gp7 FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE public.test_partition1_gp7_m2021_02 PARTITION OF public.test_partition1_gp7 FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');
CREATE TABLE public.test_partition1_gp7_m2021_03 PARTITION OF public.test_partition1_gp7 FOR VALUES FROM ('2021-03-01') TO ('2021-04-01');
-- Exchange child partition test_partition1_gp7_m2021_03 with a new one
CREATE TABLE public.test_partition1_gp7_m2021_03_new (LIKE public.test_partition1_gp7_m2021_03);
-- insert data to the new partition table
INSERT INTO public.test_partition1_gp7_m2021_03_new
SELECT * FROM public.test_partition1_gp7_m2021_03 LIMIT 100;
-- Exchange partition using EXCHANGE PARTITION syntax
ALTER TABLE public.test_partition1_gp7
EXCHANGE PARTITION test_partition1_gp7_m2022_03
WITH TABLE public.test_partition1_gp7_m2021_03_new;
-- or
ALTER TABLE public.test_partition1_gp7
EXCHANGE PARTITION m2022_03 -- if m2022_03 is the default partition name. However, got the same error
WITH TABLE public.test_partition1_gp7_m2021_03_new;ERROR: relation “public.test_partition1_gp7_1_prt_test_partition1_gp7_m2022_03” does not exist— for the EXCHANGE PARTITION m2022_03
ERROR: relation “public.test_partition1_gp7_1_prt_m2022_03" does not exist
VMware Tanzu™ Greenplum® 7.0.0-beta.2 release is available to "All Users" on TanzuNet.create function check_reptable_at_seg(tabname text, segid int) returns boolean as
$$
declare
n int;
sql text := 'select count(1) from (select * from gp_dist_random('''
|| tabname || ''') '
|| 'where (select gp_execution_segment()) = $1 except '
|| 'select * from ' || tabname || ') x';
begin
execute sql into n using segid;
return n = 0;
end;
$$
language plpgsql;
create function check_reptable(tabname text) returns boolean as
$$
declare
n int;
i int;
ok boolean;
begin
execute 'select count(distinct content) from gp_segment_configuration' into n;
i := 0;
loop
if i = n then
exit;
end if;
execute 'select check_reptable_at_seg(''' || tabname || ''', $1);' into ok using i;
if not ok then
return ok;
end if;
i := i + 1;
end loop;
return ok;
end;
$$
language plpgsql;
3 months agogpscpto gpsync. The renaming was performed to address security issues with scp. The recommendation was to replace scp with rsync (#14145). This was added to GP 7 Release Notes and the manual page was changed respectively.The user was wondering why gpscp was not bundled with GP 7 Beta. The user half jokingly shared, "Who reads Release Notes?
"Has any consideration been given to provide a symbolic link from gpsync to gpscp with a deprecation notice in the app until which time it is removed entirely? Just a thought./usr/local/gpdb7/bin/gpscp -> /usr/local/gpdb7/bin/gpsync
# bdoil @ bdoil-ub in ~/workspace/gpdb7 on git:main x [19:05:16] $ gpscp --version gpscp version 7.0.0 beta.2+dev.33.g992aa87343e build dev # bdoil @ bdoil-ub in ~/workspace/gpdb7 on git:main x [19:06:44] $ gpsync --version gpsync version 7.0.0 beta.2+dev.33.g992aa87343e build dev
podman still worked with existing docker scripts so users could alias docker=podman, should be the UX goal just my 2c
3 months agomake install command. The target could not find the gpscp utility. With GP 7, this utility's name has changed from gpscp to gpsync. In gpbackup's main repo, there is a reference to gpscp in the top-level makefile's install target (L94). Are there any plans to fix this reference when installing into a GP7 environment build from source?make install with gpbackup implicitly uses gpscp to copy gpbackup_helper binaries to GPHOME of all segments.gpadmin=# select pg_backend_pid();
pg_backend_pid
----------------
5818
(1 row)
gpadmin=# BEGIN;
gpadmin=# select * from pg_views ;
gpadmin=# select count(*),mode,locktype from pg_locks where pid=5818 group by mode, locktype;
count | mode | locktype
-------+-----------------+-----------------
1 | ExclusiveLock | virtualxid
213 | AccessShareLock | relation
1 | ExclusiveLock | distributed xid
(3 rows)
VMware Greenplum® 6.23.4 release is available to "All Users" on TanzuNet.shared_buffers = 25% of memoryeffective_cache_size = 75% of memorysynchronous_commit = off unless replication is absolutely application/business critical (many will debate, have NEVER been burned by it, no one is going to convince me, and easily see >100X greater TPS in benchmarks)max_worker_processes (GUC) and parallel_workers (GUC & storage option) will likely need to be increased and toyed with. Other GUCs include max_parallel_maintenance_workers and max_parallel_workers_per_gather. Read documentation and warnings before tuning
VMware Greenplum® Upgrade 1.6.0 release is available to "All Users" on TanzuNet.[root@gp51 gspiegel]# routeKernel IP routing tableDestination Gateway Genmask Flags Metric Ref Use Ifacedefault gateway 0.0.0.0 UG 101 0 0 eth010.10.0.0 0.0.0.0 255.255.0.0 U 101 0 0 eth0192.168.50.0 0.0.0.0 255.255.255.0 U 100 0 0 ens4[root@gp51 gspiegel]# route add -net 192.168.51.0 netmask 255.255.255.0 dev ens4[root@gp51 gspiegel]# routeKernel IP routing tableDestination Gateway Genmask Flags Metric Ref Use Ifacedefault gateway 0.0.0.0 UG 101 0 0 eth010.10.0.0 0.0.0.0 255.255.0.0 U 101 0 0 eth0192.168.50.0 0.0.0.0 255.255.255.0 U 100 0 0 ens4192.168.51.0 0.0.0.0 255.255.255.0 U 0 0 0 ens4
gpadmin=# select version();
PostgreSQL 9.4.26 (Greenplum Database 6.20.5 build commit:bd00a8103dd57c80bd98192b5b3516fd5179912b) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Jun 1 2022 22:53:46
(1 row)
Time: 2.712 ms
gpadmin=# select hashtext('etfd');
hashtext
------------
1625841569
(1 row)
gpadmin=# select version();
version
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4.24 (Greenplum Database 6.15.0 build commit:4e6288c9e9fca634b007a978fba3ce25aae26aed) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Mar 10 2021 22:30:36
(1 row)
Time: 10.179 ms
gpadmin=# select hashtext('etfd');
hashtext
------------
1625841569
(1 row)
gpadmin=# select version()
gpadmin-# ;
PostgreSQL 8.3.23 (Greenplum Database 5.29.10 build commit:fca0e6aa84a7d611ce8b7986d6fc73ae93b76f5e) on x86_64-pc-linux-gnu, compiled by GCC gcc (GCC) 6.2.0, 64-bit compiled on Oct 28 2022 01:53:56
(1 row)
gpadmin=# select hashtext('etfd');
hashtext
------------
1058755405
(1 row)postgres=# select version();
version
------------------------------------------------------
PostgreSQL 8.3.23, compiled by Visual C++ build 1400
(1 笔资料列)
postgres=# select hashtext('etfd');
hashtext
-----------
172447009
(1 笔资料列)
postgres=# select version();
version
-------------------------------------------------------------
PostgreSQL 9.2.3, compiled by Visual C++ build 1600, 64-bit
(1 行记录)
postgres=# select hashtext('etfd');
hashtext
------------
1625841569
(1 行记录)* Our source database is Oracle and DATE format it will store as a DATE and TIME combined format. * Is there any way in Greenplum we can store in the same way like DATE and TIME combined format. *Is there any server settings to suffice the above requirement? * If not we need to have a lot of development effort and time to suffice this. *If server settings are not possible is there any alternative solution which is easy to implement?
VMware Greenplum® 6.24.3 release is available to "All Users" on TanzuNet.
VMware Greenplum® 7.0.0-beta.3 release is available to "All Users" on TanzuNet.auto_explain.log_min_duration set appropriately, otherwise you won't see any output. (Set it to 0 to log everything..will be noisy..but will help if you are paranoid that you aren't seeing any output)testdb=# SELECT macaddr8_set7bit('08:00:2b:01:02:03');
macaddr8_set7bit
-------------------------
0a:00:2b:ff:fe:01:02:03
(1 row)
1 month agotest=# select * from test1 limit 2; DEBUG: Message type Q received by from libpq, len = 29 LOG: statement: select * from test1 limit 2; DEBUG: GPORCA produced plan DEBUG: Query plan size to dispatch: 0KB DEBUG: Message type M received by from libpq, len = 716 (seg0 slice1 127.0.0.1:6000 pid=1074651) DEBUG: Message type M received by from libpq, len = 716 (seg1 slice1 127.0.0.1:6001 pid=1074652) DEBUG: Message type M received by from libpq, len = 716 (seg2 slice1 127.0.0.1:6002 pid=1074653) DEBUG: Message type M received by from libpq, len = 716 (seg3 slice1 127.0.0.1:6003 pid=1074654) DEBUG: GetSockAddr socket ai_family 2 ai_socktype 2 ai_protocol 17 for 127.0.0.1 (seg0 slice1 127.0.0.1:6000 pid=1074651) DEBUG: GetSockAddr socket ai_family 2 ai_socktype 2 ai_protocol 17 for 127.0.0.1 (seg1 slice1 127.0.0.1:6001 pid=1074652) DEBUG: GetSockAddr socket ai_family 2 ai_socktype 2 ai_protocol 17 for 127.0.0.1 (seg2 slice1 127.0.0.1:6002 pid=1074653) DEBUG: GetSockAddr socket ai_family 2 ai_socktype 2 ai_protocol 17 for 127.0.0.1 (seg3 slice1 127.0.0.1:6003 pid=1074654) DEBUG: Message type M received by from libpq, len = 716 (seg5 slice1 10.128.0.22:6001 pid=745026) DEBUG: Message type M received by from libpq, len = 716 (seg7 slice1 10.128.0.22:6003 pid=745029) DEBUG: Message type M received by from libpq, len = 716 (seg9 slice1 10.128.0.21:6001 pid=707041) DEBUG: Message type M received by from libpq, len = 716 (seg10 slice1 10.128.0.21:6002 pid=707040) DEBUG: Message type M received by from libpq, len = 716 (seg4 slice1 10.128.0.22:6000 pid=745027) DEBUG: Message type M received by from libpq, len = 716 (seg6 slice1 10.128.0.22:6002 pid=745028) DEBUG: Message type M received by from libpq, len = 716 (seg11 slice1 10.128.0.21:6003 pid=707038) DEBUG: Message type M received by from libpq, len = 716 (seg8 slice1 10.128.0.21:6000 pid=707039) DEBUG: Message type M received by from libpq, len = 716 (seg13 slice1 10.128.0.20:6001 pid=722578) DEBUG: Message type M received by from libpq, len = 716 (seg15 slice1 10.128.0.20:6003 pid=722576) DEBUG: Message type M received by from libpq, len = 716 (seg12 slice1 10.128.0.20:6000 pid=722577) DEBUG: Message type M received by from libpq, len = 716 (seg14 slice1 10.128.0.20:6002 pid=722575) DEBUG: GetSockAddr socket ai_family 2 ai_socktype 2 ai_protocol 17 for 127.0.0.1 (seg4 slice1 10.128.0.22:6000 pid=745027) DEBUG: GetSockAddr socket ai_family 2 ai_socktype 2 ai_protocol 17 for 127.0.0.1 (seg8 slice1 10.128.0.21:6000 pid=707039) DEBUG: GetSockAddr socket ai_family 2 ai_socktype 2 ai_protocol 17 for 127.0.0.1 (seg7 slice1 10.128.0.22:6003 pid=745029) DEBUG: GetSockAddr socket ai_family 2 ai_socktype 2 ai_protocol 17 for 127.0.0.1 (seg10 slice1 10.128.0.21:6002 pid=707040) DEBUG: GetSockAddr socket ai_family 2 ai_socktype 2 ai_protocol 17 for 127.0.0.1 (seg5 slice1 10.128.0.22:6001 pid=745026) DEBUG: GetSockAddr socket ai_family 2 ai_socktype 2 ai_protocol 17 for 127.0.0.1 (seg6 slice1 10.128.0.22:6002 pid=745028) DEBUG: GetSockAddr socket ai_family 2 ai_socktype 2 ai_protocol 17 for 127.0.0.1 (seg15 slice1 10.128.0.20:6003 pid=722576) DEBUG: Interconnect State: isSender 1 isReceiver 0 snd_queue_depth 2 recv_queue_depth 4 Gp_max_packet_size 8192 UNACK_QUEUE_RING_SLOTS_NUM 2000 TIMER_SPAN 5000 DEFAULT_RTT 20000 hasErrors 0, ic_instance_id 3 ic_id_last_teardown 3 snd_buffer_pool.count 0 snd_buffer_pool.maxCount 0 snd_sock_bufsize 2097152 recv_sock_bufsize 2097152 snd_pkt_count 1 retransmits 0 crc_errors 0 recv_pkt_count 0 recv_ack_num 1 recv_queue_size_avg NaN capacity_avg NaN freebuf_avg 2.000000 mismatch_pkt_num 0 disordered_pkt_num 0 duplicated_pkt_num 0 rtt/dev [17510/4357, 17510.000000/4357.000000, 17510/4357] cwnd 2.000000 status_query_msg_num 0 (seg0 slice1 127.0.0.1:6000 pid=1074651) DEBUG: TeardownUDPIFCInterconnect successful (seg0 slice1 127.0.0.1:6000 pid=1074651) DEBUG: Interconnect State: isSender 1 isReceiver 0 snd_queue_depth 2 recv_queue_depth 4 Gp_max_packet_size 8192 UNACK_QUEUE_RING_SLOTS_NUM 2000 TIMER_SPAN 5000 DEFAULT_RTT 20000 hasErrors 0, ic_instance_id 3 ic_id_last_teardown 3 snd_buffer_pool.count 0 snd_buffer_pool.maxCount 0 snd_sock_bufsize 2097152 recv_sock_bufsize 2097152 snd_pkt_count 1 retransmits 0 crc_errors 0 recv_pkt_count 0 recv_ack_num 1 recv_queue_size_avg NaN capacity_avg NaN freebuf_avg 2.000000 mismatch_pkt_num 0 disordered_pkt_num 0 duplicated_pkt_num 0 rtt/dev [17508/4359, 17508.000000/4359.000000, 17508/4359] cwnd 2.000000 status_query_msg_num 0 (seg3 slice1 127.0.0.1:6003 pid=1074654) DEBUG: TeardownUDPIFCInterconnect successful (seg3 slice1 127.0.0.1:6003 pid=1074654) DEBUG: GetSockAddr socket ai_family 2 ai_socktype 2 ai_protocol 17 for 127.0.0.1 (seg9 slice1 10.128.0.21:6001 pid=707041) DEBUG: GetSockAddr socket ai_family 2 ai_socktype 2 ai_protocol 17 for 127.0.0.1 (seg11 slice1 10.128.0.21:6003 pid=707038) DEBUG: GetSockAddr socket ai_family 2 ai_socktype 2 ai_protocol 17 for 127.0.0.1 (seg12 slice1 10.128.0.20:6000 pid=722577) DEBUG: GetSockAddr socket ai_family 2 ai_socktype 2 ai_protocol 17 for 127.0.0.1 (seg13 slice1 10.128.0.20:6001 pid=722578) DEBUG: GetSockAddr socket ai_family 2 ai_socktype 2 ai_protocol 17 for 127.0.0.1 (seg14 slice1 10.128.0.20:6002 pid=722575) DEBUG: Interconnect State: isSender 1 isReceiver 0 snd_queue_depth 2 recv_queue_depth 4 Gp_max_packet_size 8192 UNACK_QUEUE_RING_SLOTS_NUM 2000 TIMER_SPAN 5000 DEFAULT_RTT 20000 hasErrors 0, ic_instance_id 3 ic_id_last_teardown 3 snd_buffer_pool.count 0 snd_buffer_pool.maxCount 0 snd_sock_bufsize 2097152 recv_sock_bufsize 2097152 snd_pkt_count 1 retransmits 0 crc_errors 0 recv_pkt_count 0 recv_ack_num 1 recv_queue_size_avg NaN capacity_avg NaN freebuf_avg 2.000000 mismatch_pkt_num 0 disordered_pkt_num 0 duplicated_pkt_num 0 rtt/dev [17514/4349, 17514.000000/4349.000000, 17514/4349] cwnd 2.000000 status_query_msg_num 0 (seg2 slice1 127.0.0.1:6002 pid=1074653) DEBUG: TeardownUDPIFCInterconnect successful (seg2 slice1 127.0.0.1:6002 pid=1074653) DEBUG: Interconnect State: isSender 1 isReceiver 0 snd_queue_depth 2 recv_queue_depth 4 Gp_max_packet_size 8192 UNACK_QUEUE_RING_SLOTS_NUM 2000 TIMER_SPAN 5000 DEFAULT_RTT 20000 hasErrors 0, ic_instance_id 3 ic_id_last_teardown 3 snd_buffer_pool.count 0 snd_buffer_pool.maxCount 0 snd_sock_bufsize 2097152 recv_sock_bufsize 2097152 snd_pkt_count 1 retransmits 0 crc_errors 0 recv_pkt_count 0 recv_ack_num 1 recv_queue_size_avg NaN capacity_avg NaN freebuf_avg 2.000000 mismatch_pkt_num 0 disordered_pkt_num 0 duplicated_pkt_num 0 rtt/dev [17518/4343, 17518.000000/4343.000000, 17518/4343] cwnd 2.000000 status_query_msg_num 0 (seg1 slice1 127.0.0.1:6001 pid=1074652) DEBUG: TeardownUDPIFCInterconnect successful (seg1 slice1 127.0.0.1:6001 pid=1074652) WARNING: interconnect may encountered a network error, please check your network (seg4 slice1 10.128.0.22:6000 pid=745027) DETAIL: Failing to send packet (seq 1) to 127.0.0.1:53470 (pid 1074605 cid -1) after 100 retries. WARNING: interconnect may encountered a network error, please check your network (seg5 slice1 10.128.0.22:6001 pid=745026) DETAIL: Failing to send packet (seq 1) to 127.0.0.1:53470 (pid 1074605 cid -1) after 100 retries. WARNING: interconnect may encountered a network error, please check your network (seg6 slice1 10.128.0.22:6002 pid=745028) DETAIL: Failing to send packet (seq 1) to 127.0.0.1:53470 (pid 1074605 cid -1) after 100 retries. WARNING: interconnect may encountered a network error, please check your network (seg7 slice1 10.128.0.22:6003 pid=745029) DETAIL: Failing to send packet (seq 1) to 127.0.0.1:53470 (pid 1074605 cid -1) after 100 retries. WARNING: interconnect may encountered a network error, please check your network (seg12 slice1 10.128.0.20:6000 pid=722577) DETAIL: Failing to send packet (seq 1) to 127.0.0.1:53470 (pid 1074605 cid -1) after 100 retries. WARNING: interconnect may encountered a network error, please check your network (seg13 slice1 10.128.0.20:6001 pid=722578) DETAIL: Failing to send packet (seq 1) to 127.0.0.1:53470 (pid 1074605 cid -1) after 100 retries. WARNING: interconnect may encountered a network error, please check your network (seg14 slice1 10.128.0.20:6002 pid=722575) DETAIL: Failing to send packet (seq 1) to 127.0.0.1:53470 (pid 1074605 cid -1) after 100 retries. WARNING: interconnect may encountered a network error, please check your network (seg15 slice1 10.128.0.20:6003 pid=722576) DETAIL: Failing to send packet (seq 1) to 127.0.0.1:53470 (pid 1074605 cid -1) after 100 retries. WARNING: interconnect may encountered a network error, please check your network (seg8 slice1 10.128.0.21:6000 pid=707039) DETAIL: Failing to send packet (seq 1) to 127.0.0.1:53470 (pid 1074605 cid -1) after 100 retries. WARNING: interconnect may encountered a network error, please check your network (seg9 slice1 10.128.0.21:6001 pid=707041) DETAIL: Failing to send packet (seq 1) to 127.0.0.1:53470 (pid 1074605 cid -1) after 100 retries. WARNING: interconnect may encountered a network error, please check your network (seg11 slice1 10.128.0.21:6003 pid=707038) DETAIL: Failing to send packet (seq 1) to 127.0.0.1:53470 (pid 1074605 cid -1) after 100 retries. WARNING: interconnect may encountered a network error, please check your network (seg10 slice1 10.128.0.21:6002 pid=707040) DETAIL: Failing to send packet (seq 1) to 127.0.0.1:53470 (pid 1074605 cid -1) after 100 retries. ^CCancel request sent LOG: Process interrupt for 'query cancel pending' (ic_udpifc.c:3819)
WARNING: oldest xmin is far in the past (seg189 58.60.201.18:6009 pid=76049) when vacuum any tables in their user db, we checked and confirmed that there is no long running idle or non-idle query from pg_stat_activity, no pg_prepared_xacts even when connected to seg189 via utility mode, gpstate -s implies the primary was synchronized.. while being aware of this might be resolved after restarting the db, they are still eager to know how this could happen? they are running on 6.19.3hyxf_adb and gpperfmon.) Recommendation would be to take downtime and run vacuum freeze on all databases.gpadmin 25859 0.7 0.0 921868 518184 ? Ss Mar29 716:26 /usr/local/greenplum-db-6.19.3/bin/postgres -D /data9/primary/gpseg189 -p 6009 ... gpadmin 52002 0.0 0.0 995120 14664 ? SNsl May17 1:31 postgres: 6009, etl hyxf_adb 58.60.201.1(58526) con1868036 seg189 cmd2 slice1 MPPEXEC INSERT
, thank you so much for answering me!hyxf_adb=# select datname,age(datfrozenxid) from pg_database order by 2 desc; datname | age -----------+---------- template0 | 32304991 hyxf_adb | 4402892 gpperfmon | 3984146 es | 3983249 test | 3983247 template1 | 3983227 pgbenchdb | 887015 postgres | 887015
6 version of that so. Do you know which executalbe/so file is complaining about that?Postgis to be precise.Upgrading PostGIS 2.1.5 or 2.5.4 section of the doc needs an update based on PostGIS 2.5.4+pivotal.8.build.1 I will raise a docs jira for it.gppkg -u <package_name>
/etc/sysctl.d/ called local.conf. I "prefer" using /etc over /usr/lib because a general recommendation seems to be that config in /etc is for the local system user while /usr locations are for packages to install. Just my
.
VMware Greenplum® 7.0.0-beta.4 release is available to "All Users" on TanzuNet.[gpadmin@matt-gpdb7-aio][lib]$ cat /etc/redhat-release CentOS Stream release 8 [gpadmin@matt-gpdb7-aio][lib]$ uname -a Linux matt-gpdb7-aio 4.18.0-489.el8.x86_64 #1 SMP Thu Apr 27 17:02:11 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux
No module named '_pg')gpinitsystem -c /opt/greenplum_7.0.0/gpinitsystem_config -h /opt/greenplum_7.0.0/seg_hosts -a ... 20230624:01:05:35:308603 gpinitsystem:matt-gpdb7-aio:gpadmin-[INFO]:------------------------------------------------ 20230624:01:05:35:308603 gpinitsystem:matt-gpdb7-aio:gpadmin-[INFO]:-Parallel process exit status 20230624:01:05:35:308603 gpinitsystem:matt-gpdb7-aio:gpadmin-[INFO]:------------------------------------------------ 20230624:01:05:35:308603 gpinitsystem:matt-gpdb7-aio:gpadmin-[INFO]:-Total processes marked as completed = 2 20230624:01:05:35:308603 gpinitsystem:matt-gpdb7-aio:gpadmin-[INFO]:-Total processes marked as killed = 0 20230624:01:05:35:308603 gpinitsystem:matt-gpdb7-aio:gpadmin-[INFO]:-Total processes marked as failed = 0 20230624:01:05:35:308603 gpinitsystem:matt-gpdb7-aio:gpadmin-[INFO]:------------------------------------------------ 20230624:01:05:35:308603 gpinitsystem:matt-gpdb7-aio:gpadmin-[INFO]:-Removing back out file 20230624:01:05:35:308603 gpinitsystem:matt-gpdb7-aio:gpadmin-[INFO]:-No errors generated from parallel processes 20230624:01:05:35:308603 gpinitsystem:matt-gpdb7-aio:gpadmin-[INFO]:-Restarting the Greenplum instance in production mode Error: unable to import module: No module named '_pg' 20230624:01:05:35:308603 gpinitsystem:matt-gpdb7-aio:gpadmin-[FATAL]:-Error from Greenplum instance shutdown, check log files Script Exiting!
$ gpstart Error: unable to import module: No module named '_pg'
[gpadmin@matt-gpdb7-aio][~]$ ssh localhost python3 --version Python 3.9.16 [gpadmin@matt-gpdb7-aio][~]$ python3 --version Python 3.9.16 [gpadmin@matt-gpdb7-aio][~]$ pip3 --version pip 20.2.4 from /usr/lib/python3.9/site-packages/pip (python 3.9)
$GPHOME/lib/python/pg.pytry:
from _pg import *[gpadmin@matt-gpdb7-aio][lib]$ /usr/bin/python3 -c "from _pg import *" ; echo $? Traceback (most recent call last): File "<string>", line 1, in <module> ModuleNotFoundError: No module named '_pg' 1 [gpadmin@matt-gpdb7-aio][lib]$ /usr/bin/python3.6 -c "from _pg import *" ; echo $? 0 [gpadmin@matt-gpdb7-aio][lib]$ /usr/bin/python3.9 -c "from _pg import *" ; echo $? Traceback (most recent call last): File "<string>", line 1, in <module> ModuleNotFoundError: No module named '_pg' 1